package cn.com.headfree.mouldshow.dao.impl;

import cn.com.headfree.entity.TSdtTutorPrentInfo;
import cn.com.headfree.mouldshow.dao.TSdtTutorPrentInfoDao;
import cn.com.headfree.vo.simple.SdtTutorPrentInfoVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("/tSdtTutorPrentInfoDao")
public class TSdtTutorPrentInfoDaoImpl extends BaseDaoImpl<TSdtTutorPrentInfo> implements TSdtTutorPrentInfoDao {


    @Override
    public List<SdtTutorPrentInfoVo> findByBaseorgId(String deptId) throws Exception{
        Object[] params = new Object[1];
        StringBuilder sql = new StringBuilder("SELECT t1.id,t1.start_date,t1.end_date,t1.erp_no,t1.tutor_name,tdpi.erp_no AS prent_erp_no,up.`NAME` AS user_name,t1.teach_summary,GROUP_CONCAT(tdpi.win_info) AS prent_award_name,tdpi.id AS award_id FROM(SELECT tstpi.id,tstpi.start_date,tstpi.end_date,tstpi.teach_summary,tstpi.erp_no,tstpi.is_valid,tstpi.del_sign,ut.`NAME` AS tutor_name FROM t_sdt_tutor_prent_info tstpi INNER JOIN t_dept_emp_rela d ON tstpi.erp_no = d.erp_no INNER JOIN ( SELECT * FROM t_org_dept o WHERE o.path LIKE ? ) o2 ON o2.id = d.dept_id LEFT JOIN isc_user ut ON tstpi.erp_no = ut.FAX ) t1 INNER JOIN t_sdt_prent_info tdpi ON t1.id = tdpi.sdt_id LEFT JOIN isc_user up on tdpi.erp_no=up.FAX WHERE t1.is_valid = '0' AND t1.del_sign = '0' GROUP BY tdpi.erp_no ORDER BY t1.start_date DESC");
//        StringBuilder sql = new StringBuilder("SELECT t1.id,t1.start_date,DATE_FORMAT(t1.start_date,'%Y-%m-%d %H:%i:%s') AS start_date_str,t1.end_date,DATE_FORMAT(t1.end_date,'%Y-%m-%d %H:%i:%s') AS end_date_str,t1.erp_no,t1.tutor_name,tdpi.erp_no AS prent_erp_no,up.`NAME` AS user_name,t1.teach_summary,GROUP_CONCAT(tdpi.win_info) AS prent_award_name,tdpi.id AS award_id FROM(SELECT tstpi.id,tstpi.start_date,tstpi.end_date,tstpi.teach_summary,tstpi.erp_no,tstpi.is_valid,tstpi.del_sign,ut.`NAME` AS tutor_name FROM t_sdt_tutor_prent_info tstpi INNER JOIN t_dept_emp_rela d ON tstpi.erp_no = d.erp_no INNER JOIN ( SELECT * FROM t_org_dept o WHERE o.path LIKE ? ) o2 ON o2.id = d.dept_id LEFT JOIN isc_user ut ON tstpi.erp_no = ut.FAX ) t1 INNER JOIN t_sdt_prent_info tdpi ON t1.id = tdpi.sdt_id LEFT JOIN isc_user up on tdpi.erp_no=up.FAX WHERE t1.is_valid = '0' AND t1.del_sign = '0' GROUP BY tdpi.erp_no ORDER BY t1.start_date DESC");
//        StringBuilder sql = new StringBuilder("select tstpi.id,tstpi.start_date,DATE_FORMAT(tstpi.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,tstpi.end_date,DATE_FORMAT(tstpi.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,tstpi.erp_no,ut.`NAME` as tutor_name,tdpi.erp_no as prent_erp_no,up.`NAME` as user_name,tstpi.teach_summary,GROUP_CONCAT(tdpi.win_info) as prent_award_name,tdpi.id as award_id from t_sdt_tutor_prent_info tstpi INNER JOIN t_dept_emp_rela d on tstpi.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id INNER JOIN isc_user ut on tstpi.erp_no=ut.FAX INNER JOIN t_sdt_prent_info tdpi on tstpi.id=tdpi.sdt_id LEFT JOIN isc_user up on tdpi.erp_no=up.FAX WHERE tstpi.is_valid='0' AND tstpi.del_sign='0' GROUP BY tdpi.erp_no ORDER BY tstpi.start_date DESC");
//        StringBuilder sql = new StringBuilder("select tstpi.id,tstpi.start_date,DATE_FORMAT(tstpi.start_date,'%Y-%m-%d %H:%i:%s') as start_date_str,tstpi.end_date,DATE_FORMAT(tstpi.end_date,'%Y-%m-%d %H:%i:%s') as end_date_str,tstpi.erp_no,ut.xb_name as tutor_name,tdpi.erp_no as prent_erp_no,up.xb_name as user_name,tstpi.teach_summary,GROUP_CONCAT(tdpi.win_info) as prent_award_name,tdpi.id as award_id from t_sdt_tutor_prent_info tstpi INNER JOIN t_dept_emp_rela d on tstpi.erp_no=d.erp_no INNER JOIN (select * from t_org_dept o where o.path like ?) o2 on o2.id=d.dept_id INNER JOIN t_isc_user_tmp ut on tstpi.erp_no=ut.hr_erp INNER JOIN t_sdt_prent_info tdpi on tstpi.id=tdpi.sdt_id INNER JOIN t_isc_user_tmp up on tdpi.erp_no=up.hr_erp WHERE tstpi.is_valid='0' AND tstpi.del_sign='0' GROUP BY up.xb_name ORDER BY tstpi.start_date DESC");
        params[0] = "%"+deptId+"%";
        return this.findAllBySQL(SdtTutorPrentInfoVo.class, sql.toString(), null, false, params);
    }
}
